﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Light.Utility;
using System.Collections;

namespace Light.Framework
{
    /// <summary>
    /// 实体T 查询器
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class EntityMediator<T> where T : new()
    {
        //数据库支持的最大参数数量（SQL Server为2100，不超过最大值即可）
        private const int MAX_PARAMETER_QUANTITY = 1500;

        private static ColumnInfo _primaryKey = null;
        /// <summary>
        /// 主键列
        /// </summary>
        protected static ColumnInfo PrimaryKey
        {
            get
            {
                if (_primaryKey == null)
                    _primaryKey = DbMapper.GetPrimaryKey<T>();
                return _primaryKey;
            }
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static OperateResult Create(T obj)
        {
            OperateResult result = new OperateResult();

            StringBuilder builderSql = new StringBuilder();
            List<IDataParameter> listParam = new List<IDataParameter>();

            //获取表名和主键
            string tableName = DbMapper.GetTableName(typeof(T));
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey(typeof(T));

            builderSql.Append(SQLBuilder.BuildInsertSql(obj, tableName, listParam, 0));
            builderSql.Append(SQLBuilder.BuildManyToManySql(obj, primaryKey, ManyToManyAction.Create));

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(builderSql.ToString(), listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "新增成功";
                }
            }
            return result;
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="objs"></param>
        /// <returns></returns>
        public static OperateResult Create(List<T> objs)
        {
            OperateResult result = new OperateResult();
            StringBuilder builder = new StringBuilder();
            List<IDataParameter> listParam = new List<IDataParameter>();

            //获取表名和主键
            string tableName = DbMapper.GetTableName(typeof(T));
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey(typeof(T));

            int countSucced = 0;
            int length = objs.Count;

            for (int i = 0; i < length; i++)
            {
                builder.Append(SQLBuilder.BuildInsertSql(objs[i], tableName, listParam, i));
                builder.Append(SQLBuilder.BuildManyToManySql(objs[i], primaryKey, ManyToManyAction.Create));

                if ((listParam.Count >= MAX_PARAMETER_QUANTITY || i == length - 1) && builder.Length > 0)
                {
                    using (ISQLExecuter executer = SQLManager.CreateExecuter())
                    {
                        countSucced += executer.ExecuteNonQuery(builder.ToString(), listParam.ToArray());

                        builder.Clear();
                        listParam.Clear();
                    }
                }
            }

            if (countSucced > 0)
            {
                result.IsSucced = true;
                result.Message = "新增成功";
            }
            return result;
        }


        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids">主键ID值</param>
        /// <returns></returns>
        public static OperateResult Delete(params string[] ids)
        {
            OperateResult result = new OperateResult();

            if (ids == null || ids.Length == 0)
            {
                result.Message = "没有指定要删除的记录";
                return result;
            }

            string tableName = DbMapper.GetTableName<T>();
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey<T>();
            if (primaryKey == null)
                throw new Exception(string.Format("删除失败，类型 {0} 未映射主键 PrimaryKeyAttribute", typeof(T).Name));

            List<IDataParameter> listParam = new List<IDataParameter>();

            StringBuilder builder = new StringBuilder();
            int length = ids.Length;
            int countSucced = 0;
            for (int i = 0; i < length; i++)
            {
                if (!String.IsNullOrEmpty(ids[i]))
                {
                    T obj = new T();
                    ReflectionHelper.SetPropertyValue(obj, primaryKey.PropertyName, ids[i]);
                    builder.Append(SQLBuilder.BuildDeleteSql(tableName, primaryKey, listParam, ids[i], i));
                    builder.Append(SQLBuilder.BuildManyToManySql(obj, primaryKey, ManyToManyAction.Delete));
                }

                if ((listParam.Count >= MAX_PARAMETER_QUANTITY || i == length - 1) && builder.Length > 0)
                {
                    using (ISQLExecuter excuter = SQLManager.CreateExecuter())
                    {
                        countSucced += excuter.ExecuteNonQuery(builder.ToString(), listParam.ToArray());
                    }

                    builder.Clear();
                    listParam.Clear();
                }
            }
            if (countSucced > 0)
            {
                result.IsSucced = true;
                result.Message = "删除成功";
            }
            return result;
        }


        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="obj">主键映射的字段必须有值</param>
        /// <returns></returns>
        public static OperateResult Delete(T obj)
        {
            OperateResult result = new OperateResult();
            string tableName = DbMapper.GetTableName<T>();
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey<T>();
            if (primaryKey == null)
                throw new Exception(string.Format("删除失败，类型 {0} 未映射主键 PrimaryKeyAttribute", typeof(T).Name));

            object pValue = ReflectionHelper.GetPropertyValue(obj, primaryKey.PropertyName); ;
            if (pValue == null)
            {
                throw new Exception(string.Format("删除失败，关联主键的属性 {0} 值不能为空", primaryKey.PropertyName));
            }

            List<IDataParameter> listParam = new List<IDataParameter>();
            string sql = SQLBuilder.BuildDeleteSql(tableName, primaryKey, listParam, pValue, 0);
            sql += SQLBuilder.BuildManyToManySql(obj, primaryKey, ManyToManyAction.Delete);
            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(sql, listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "删除成功";
                }
            }
            return result;
        }

        /// <summary>
        /// 更新（默认不更新多对多关系表,如需更新，请调用带 ManyToManyAction 参数的方法）
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>

        public static OperateResult Update(T obj)
        {
            return Update(obj, null);
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="action">多对多操作</param>
        /// <returns></returns>
        public static OperateResult Update(T obj, UpdateColumn saveColumn)
        {
            OperateResult result = new OperateResult();
            StringBuilder builderSql = new StringBuilder();
            List<IDataParameter> listParam = new List<IDataParameter>();

            //获取表名
            string tableName = DbMapper.GetTableName<T>();
            //获取主键
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey<T>();

            if (saveColumn == null)
                saveColumn = new UpdateColumn(typeof(T));
            if (!saveColumn.Include(primaryKey.PropertyName))
                saveColumn.Add(primaryKey.PropertyName);

            builderSql.Append(SQLBuilder.BuildUpdateSql(obj, listParam, tableName, primaryKey, 0, saveColumn));

            if (saveColumn.IsManyToManyUpdate)
            {
                builderSql.Append(SQLBuilder.BuildManyToManySql(obj, primaryKey, ManyToManyAction.Update, saveColumn));
            }

            //执行SQL
            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(builderSql.ToString(), listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "更新成功";
                }
            }

            return result;
        }

        /// <summary>
        /// 批量更新（默认不不更新多对多关系表,如需更新，请调用带 ManyToManyAction 参数的方法）
        /// </summary>
        /// <param name="objs"></param>
        /// <returns></returns>
        public static OperateResult Update(List<T> objs)
        {
            return Update(objs, null);
        }
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="objs"></param>
        /// <returns></returns>
        public static OperateResult Update(List<T> objs, UpdateColumn saveColumn)
        {
            OperateResult result = new OperateResult();

            //获取表名
            string tableName = DbMapper.GetTableName<T>();
            //获取主键
            ColumnInfo primaryKey = DbMapper.GetPrimaryKey<T>();
            //获取系统类型列
            List<ColumnInfo> columns = DbMapper.GetColumns<T>();

            StringBuilder builder = new StringBuilder();
            List<IDataParameter> listParam = new List<IDataParameter>();

            if (saveColumn == null)
                saveColumn = new UpdateColumn(typeof(T));

            if (!saveColumn.Include(primaryKey.PropertyName))
                saveColumn.Add(primaryKey.PropertyName);


            int countSucced = 0;
            int length = objs.Count;
            for (int i = 0; i < length; i++)
            {
                builder.Append(SQLBuilder.BuildUpdateSql(objs[i], listParam, tableName, primaryKey, i, saveColumn));

                if (saveColumn.IsManyToManyUpdate)
                    builder.Append(SQLBuilder.BuildManyToManySql(objs[i], primaryKey, ManyToManyAction.Update, saveColumn));

                if ((listParam.Count >= MAX_PARAMETER_QUANTITY || i == length - 1) && builder.Length > 0)
                {
                    using (ISQLExecuter executer = SQLManager.CreateExecuter())
                    {
                        countSucced += executer.ExecuteNonQuery(builder.ToString(), listParam.ToArray());
                    }
                    builder.Clear();
                    listParam.Clear();
                }
            }

            if (countSucced > 0)
            {
                result.IsSucced = true;
                result.Message = "更新成功";
            }

            return result;
        }

        /// <summary>
        /// 详情（支持外键关联查询、多对多关联查询）
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        public static T Get(string id)
        {
            return Get(id, null);
        }

        /// <summary>
        /// 详情（支持关联查询）
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <param name="resultColumn">不需要查询的结果属性集合</param>
        /// <returns></returns>
        public static T Get(string id, ResultColumn resultColumn)
        {
            T obj = default(T);

            #region 查询参数

            string paramName = "@" + PrimaryKey.Name;
            IDataParameter p = new DataParameter();
            p.ParameterName = paramName;
            p.Value = id;

            #endregion

            #region 主表及外键关联表查询SQL

            string select = SQLBuilder.BuildSelectSql<T>(true, resultColumn);
            string from = SQLBuilder.BuildFromSql<T>(resultColumn, null);
            string where = " WHERE ";

            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T>();

            if (dicBelongs != null && dicBelongs.Count > 0)
                where += DbMapper.PRIMARY_TABLE_ALIAS + "." + PrimaryKey.Name + "=" + paramName;
            else
                where += PrimaryKey.Name + "=" + paramName;
            string sql = string.Format("SELECT {0} FROM {1} {2}", select, from, where);

            #endregion

            #region 处理多对多查询SQL

            Dictionary<string, ManyToManyInfo> dicManyToMany = DbMapper.GetManyToMany(typeof(T));

            if (dicManyToMany != null && dicManyToMany.Count > 0)
            {
                StringBuilder builder = new StringBuilder();
                string tableName = DbMapper.GetTableName<T>();


                foreach (var key in dicManyToMany.Keys)
                {

                    ManyToManyInfo many = dicManyToMany[key];

                    //关联表
                    string tableName_ref = DbMapper.GetTableName(many.Type);
                    ColumnInfo refKey = DbMapper.GetPrimaryKey(many.Type);

                    builder.Append(" SELECT " + tableName_ref + ".* FROM  " + tableName + " " + DbMapper.PRIMARY_TABLE_ALIAS);
                    builder.Append(" JOIN " + many.Table + " " + many.Alias);
                    builder.Append(" ON " + many.Alias + "." + many.ColumnKey + "=" + DbMapper.PRIMARY_TABLE_ALIAS + "." + PrimaryKey.Name);


                    builder.Append(" JOIN " + tableName_ref + " " + tableName_ref);
                    builder.Append(" ON " + many.Alias + "." + many.ColumnRef + "=" + tableName_ref + "." + refKey.Name);

                }

                where = " WHERE " + DbMapper.PRIMARY_TABLE_ALIAS + "." + PrimaryKey.Name + "=" + paramName;
                builder.Append(where);

                sql += ";" + builder.ToString();
            }

            #endregion

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                DataSet ds = executer.ExecuteDataset(sql, p);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    obj = ConvertToEntity<T>(ds.Tables[0])[0];

                    #region 多对多关联查询结果

                    int tableCount = ds.Tables.Count;
                    if (tableCount > 1)
                    {
                        foreach (var key in dicManyToMany.Keys)
                        {
                            ManyToManyInfo many = dicManyToMany[key];

                            //将多对多表的集合项
                            for (int i = 1; i < tableCount; i++)
                            {
                                if (ds.Tables[i].Rows.Count > 0)
                                {
                                    IList refs = ConvertToEntity(many.Type, ds.Tables[i]);
                                    ReflectionHelper.SetPropertyValue(obj, key, refs);
                                }
                            }
                        }

                    }

                    #endregion
                }
            }
            return obj;
        }

        /// <summary>
        /// 换取第一个对象
        /// </summary>
        /// <returns></returns>
        public static T Get()
        {
            T obj = default(T);

            #region 查询SQL

            string select = SQLBuilder.BuildSelectSql<T>(true, null);
            string from = SQLBuilder.BuildFromSql<T>(null, null);
            string sql = string.Format("SELECT  {0} FROM {1} ", select, from);

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                DataSet ds = executer.ExecuteDataset(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    obj = ConvertToEntity<T>(ds.Tables[0])[0];
                }
            }

            #endregion

            return obj;
        }


        /// <summary>
        /// 获取符合条件的一条记录
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <param name="resultColumn">不需要查询的结果属性集合</param>
        /// <returns></returns>
        public static T Get(SearchArgs searchArgs, ResultColumn resultColumn)
        {
            T obj = default(T);
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultColumn == null)
                resultColumn = new ResultColumn();

            string where = searchArgs.ToString<T>();
            List<IDataParameter> listParams = searchArgs.GetDataParameters<T>();

            string select = SQLBuilder.BuildSelectSql<T>(true, resultColumn);
            string from = SQLBuilder.BuildFromSql<T>(resultColumn, searchArgs);

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string sql = string.Format(@"SELECT {0} FROM {1} {2}", select, from, where);
                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                {
                    IList<T> objs = ConvertToEntity<T>(ds.Tables[0]);
                    if (objs != null && objs.Count > 0)
                        obj = objs[0];
                }
            }
            return obj;
        }

        /// <summary>
        /// 返回记录条数
        /// </summary>
        /// <param name="searchArgs">允许为null</param>
        public static int Count(SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string where = searchArgs.ToString<T>();
            string from = SQLBuilder.BuildFromSql<T>(null, searchArgs);
            List<IDataParameter> listParams = searchArgs.GetDataParameters<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string sql = string.Format(@"SELECT COUNT(*) FROM {0} {1}", from, where);

                object result = executer.ExecuteScalar(sql, listParams.ToArray());
                return StringHelper.ToInt(result);
            }
        }

        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null，为null时以主键排序</param>
        /// <param name="resultColumn">需要查询的结果属性名集合</param>
        /// <returns></returns>
        public static List<T> GetAll(SearchArgs searchArgs, ResultOrder resultOrder, ResultColumn resultColumn)
        {
            DataTable dt = GetEntitiesTable(searchArgs, resultOrder, resultColumn, -1);
            return ConvertToEntity<T>(dt);
        }

        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null，为null时以主键排序</param>
        /// <param name="resultColumn">需要查询的结果属性名集合</param>
        /// <param name="resultCount">需要获取的结果数量，为-1时获取全部</param>
        /// <returns></returns>
        public static List<T> GetAll(SearchArgs searchArgs, ResultOrder resultOrder, ResultColumn resultColumn, int resultCount)
        {
            DataTable dt = GetEntitiesTable(searchArgs, resultOrder, resultColumn, resultCount);
            return ConvertToEntity<T>(dt);
        }

        /// <summary>
        /// 分页查询，返回实体集合
        /// </summary>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="pageArgs">分页参数，不能为null</param>
        /// <param name="resultOrder">排序参数，可为null，为null时以主键排序</param>
        /// <param name="resultColumn">需要查询的结果属性名集合</param>
        /// <returns></returns>
        public static List<T> Search(SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder, ResultColumn resultColumn)
        {

            List<T> list = new List<T>();
            System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch();
            sw1.Start();

            DataTable dt = GetEntitiesTable(searchArgs, pageArgs, resultOrder, resultColumn);

            sw1.Stop();

            if (dt != null && dt.Rows.Count > 0)
            {
                System.Diagnostics.Stopwatch sw2 = new System.Diagnostics.Stopwatch();
                sw2.Start();

                list = ConvertToEntity<T>(dt);

                sw2.Stop();
            }
            return list;
        }

        /// <summary>
        /// 金额求和
        /// </summary>
        /// <param name="propertyName">属性名</param>
        /// <param name="searchArgs">查询参数</param>
        /// <returns></returns>
        public static decimal Sum(string propertyName, SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            ResultColumn resultColumn = new ResultColumn();

            string columnName = DbMapper.GetColumnName(typeof(T), propertyName); //获取属性名对应的列表
            string where = searchArgs.ToString<T>();

            resultColumn.Add(propertyName);
            string from = SQLBuilder.BuildFromSql<T>(resultColumn, searchArgs);

            List<IDataParameter> listParams = searchArgs.GetDataParameters<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string sql = string.Format(@"SELECT SUM({0}) FROM {1} {2}", columnName, from, where);

                object result = executer.ExecuteScalar(sql, listParams.ToArray());
                return StringHelper.ToDecimal(result);
            }
        }

        #region 私有方法

        /// <summary>
        ///  将数据表转实体（关联表）
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static List<T1> ConvertToEntity<T1>(DataTable dt) where T1 : new()
        {
            List<T1> list = new List<T1>();
            int idx = 0;
            int count = dt.Rows.Count;

            List<ColumnInfo> columns = DbMapper.GetColumns<T1>();
            Dictionary<string, object> values = new Dictionary<string, object>();

            //逐行转换
            for (int i = 0; i < count; i++)
            {
                values.Clear();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn col in dt.Columns)
                {
                    //将列名替换成属性名
                    string cname = col.ColumnName.Replace(DbMapper.PREFIX_TABLE_ALIAS + idx + "_", "");
                    ColumnInfo c = columns.Find(t => t.Name == cname);
                    if (c != null)
                        values.Add(c.PropertyName, dr[col]);
                }

                // 为 obj 基本类型赋值
                T1 obj = new T1();
                ReflectionHelper.SetPropertyValue(obj, values);
                list.Add(obj);
            }

            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T1>();
            if (dicBelongs != null && dicBelongs.Count > 0)

                //遍历关联属性
                foreach (string propertyName in dicBelongs.Keys)
                {
                    idx++;
                    BelongsInfo bc = dicBelongs[propertyName];
                    Type type = bc.Type;

                    int cascade = propertyName.Split('.').Length;
                    columns = DbMapper.GetColumns(type, cascade);

                    //逐行转换
                    for (int i = 0; i < count; i++)
                    {
                        values.Clear();
                        DataRow dr = dt.Rows[i];
                        foreach (DataColumn col in dt.Columns)
                        {
                            string cname = col.ColumnName.Replace(bc.Alias + "_", "");
                            ColumnInfo c = columns.Find(t => t.Name == cname);
                            if (c != null && !values.ContainsKey(propertyName + "." + c.PropertyName))
                                values.Add(propertyName + "." + c.PropertyName, dr[col]);
                        }
                        ReflectionHelper.SetPropertyValue(list[i], values);
                    }
                }
            return list;
        }


        /// <summary>
        ///  将数据表转实体（关联表）
        /// </summary>
        /// <param name="objType">对象类型</param>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static IList ConvertToEntity(Type objType, DataTable dt)
        {
            IList list = new List<object>();
            int idx = 0;
            int count = dt.Rows.Count;

            List<ColumnInfo> columns = DbMapper.GetColumns(objType, 0);
            Dictionary<string, object> values = new Dictionary<string, object>();

            //逐行转换
            for (int i = 0; i < count; i++)
            {
                values.Clear();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn col in dt.Columns)
                {
                    //将列名替换成属性名
                    string cname = col.ColumnName.Replace(DbMapper.PREFIX_TABLE_ALIAS + idx + "_", "");
                    ColumnInfo c = columns.Find(t => t.Name == cname);
                    if (c != null)
                        values.Add(c.PropertyName, dr[col]);
                }

                // 为 obj 基本类型赋值
                object obj = Activator.CreateInstance(objType);
                ReflectionHelper.SetPropertyValue(obj, values);
                list.Add(obj);
            }

            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic(objType);
            if (dicBelongs != null && dicBelongs.Count > 0)

                //遍历关联属性
                foreach (string propertyName in dicBelongs.Keys)
                {
                    idx++;
                    BelongsInfo bc = dicBelongs[propertyName];
                    Type type = bc.Type;

                    int cascade = propertyName.Split('.').Length;
                    columns = DbMapper.GetColumns(type, cascade);

                    //逐行转换
                    for (int i = 0; i < count; i++)
                    {
                        values.Clear();
                        DataRow dr = dt.Rows[i];
                        foreach (DataColumn col in dt.Columns)
                        {
                            string cname = col.ColumnName.Replace(bc.Alias + "_", "");
                            ColumnInfo c = columns.Find(t => t.Name == cname);
                            if (c != null && !values.ContainsKey(propertyName + "." + c.PropertyName))
                                values.Add(propertyName + "." + c.PropertyName, dr[col]);
                        }
                        ReflectionHelper.SetPropertyValue(list[i], values);
                    }
                }
            return list;
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <param name="resultOrder"></param>
        /// <param name="resultColumn">需要查询的结果属性集合</param>
        /// <param name="resultCount">需要的结果条数</param>
        /// <returns></returns>
        private static DataTable GetEntitiesTable(SearchArgs searchArgs, ResultOrder resultOrder, ResultColumn resultColumn, int resultCount)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();

            string select = SQLBuilder.BuildSelectSql<T>(false, resultColumn);
            string from = SQLBuilder.BuildFromSql<T>(resultColumn, searchArgs);

            string where = searchArgs.ToString<T>();
            string order = resultOrder.ToString<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {

                string topSql = resultCount > 0 ? " TOP " + resultCount + " " : "";
                string selectSql = string.Format(@"SELECT {0} {1} FROM {2} {3} ORDER BY {4}", topSql, select, from, where, order);

                List<IDataParameter> listParams = searchArgs.GetDataParameters<T>();

                DataSet ds = executer.ExecuteDataset(selectSql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        /// <summary>
        /// 分页查询，返回转实体用的DataTable
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <param name="pageArgs"></param>
        /// <param name="resultOrder"></param>
        /// <param name="resultColumn">需要查询的结果属性集合</param>
        /// <returns></returns>
        private static DataTable GetEntitiesTable(SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder, ResultColumn resultColumn)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();
            if (pageArgs == null)
                throw new Exception("分页参数 PageArgs 不能为空");

            string selectSql = SQLBuilder.BuildSelectSql<T>(false, resultColumn);
            string fromSql = SQLBuilder.BuildFromSql<T>(resultColumn, searchArgs);

            string whereSql = searchArgs.ToString<T>();
            string orderSql = resultOrder.ToString<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                //获取总记录条数
                string countSql = string.Format("SELECT COUNT(*) FROM {0} {1}", fromSql, whereSql);

                List<IDataParameter> listParams = searchArgs.GetDataParameters<T>();
                pageArgs.TotalResults = StringHelper.ToInt(executer.ExecuteScalar(countSql, listParams.ToArray()));

                //查询结果集
                string sql = string.Format(@"
                                SELECT {0}
                                FROM {1}
                                 {2}
                                ORDER BY {3}
                                OFFSET {4} ROWS
                                FETCH NEXT {5} ROWS ONLY", selectSql, fromSql, whereSql, orderSql, (pageArgs.PageIndex - 1) * pageArgs.PageSize, pageArgs.PageSize);

                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }
        #endregion
    }
}
